-- Artifact -----------------------------------------------------------
CREATE TABLE Artifact  (
    ConceptDelegateID_FK	bigint NOT NULL,
    GroupId             	varchar(64) NOT NULL,
    ArtifactId          	varchar(256) NOT NULL,
    Version             	varchar(64) NOT NULL,
    Classifier          	varchar(64) ,
    Description         	varchar(2048) ,
    MimeType            	varchar(32) ,
    Caption             	varchar(1024) ,
    Reference           	varchar(1024) NOT NULL,
    Credit              	varchar(1024) ,
    id                  	bigint NOT NULL,
    LAST_UPDATED_TIME   	timestamp NOT NULL,
    CreationDate        	timestamp ,
    CONSTRAINT Artifact_PK PRIMARY KEY(id)
)
GO
ALTER TABLE Artifact
    ADD CONSTRAINT Artifact_FK1
	FOREIGN KEY(ConceptDelegateID_FK)
	REFERENCES ConceptDelegate(id)
	ON DELETE NO ACTION
	ON UPDATE NO ACTION
GO
CREATE INDEX idx_Artifact_LUT
    ON Artifact(LAST_UPDATED_TIME)
GO

CREATE UNIQUE INDEX idx_Artifact_CK
    ON Artifact(ConceptDelegateID_FK, GroupId, ArtifactId, Version, Classifier)
GO
CREATE INDEX idx_Artifact_FK1
    ON Artifact(ConceptDelegateID_FK)
GO

ALTER TABLE Association
	ADD LAST_UPDATED_TIME TIMESTAMP
GO

ALTER TABLE CameraData
    ADD LogDTG TIMESTAMP
GO

ALTER TABLE CameraData
    ADD LAST_UPDATED_TIME TIMESTAMP
GO

ALTER TABLE CameraData
    ADD X FLOAT
GO

ALTER TABLE CameraData
    ADD Y FLOAT
GO

ALTER TABLE CameraData
    ADD Z FLOAT
GO

ALTER TABLE CameraData
    ADD PITCH FLOAT
GO

ALTER TABLE CameraData
    ADD ROLL FLOAT
GO

ALTER TABLE CameraData
    ADD XYUNITS VARCHAR(50)
GO

ALTER TABLE CameraData
    ADD ZUNITS VARCHAR(50)
GO

ALTER TABLE CameraData
    ADD HEADING FLOAT
GO

ALTER TABLE CameraData
    ADD VIEWHEIGHT FLOAT
GO

ALTER TABLE CameraData
    ADD VIEWWIDTH FLOAT
GO

ALTER TABLE CameraData
    ADD VIEWUNITS FLOAT
GO

ALTER TABLE CameraPlatformDeployment
    ADD LAST_UPDATED_TIME TIMESTAMP
GO

ALTER TABLE Concept
    ADD LAST_UPDATED_TIME TIMESTAMP
GO

ALTER TABLE ConceptDelegate
    ADD LAST_UPDATED_TIME TIMESTAMP
GO


ALTER TABLE ConceptName
    ADD LAST_UPDATED_TIME TIMESTAMP
GO

ALTER TABLE History
    ADD LAST_UPDATED_TIME TIMESTAMP
GO

ALTER TABLE LinkRealization
    ADD LAST_UPDATED_TIME TIMESTAMP
GO

ALTER TABLE LinkTemplate
    ADD LAST_UPDATED_TIME TIMESTAMP
GO

ALTER TABLE Media
    ADD LAST_UPDATED_TIME TIMESTAMP
GO

ALTER TABLE Observation
    ADD LAST_UPDATED_TIME TIMESTAMP
GO

ALTER TABLE Observation
    ADD X DOUBLE
GO

ALTER TABLE Observation
    ADD Y DOUBLE
GO

ALTER TABLE PhysicalData
    ADD LogDTG TIMESTAMP
GO

ALTER TABLE PhysicalData
    ADD LAST_UPDATED_TIME TIMESTAMP
GO

ALTER TABLE USAGE
    ADD LAST_UPDATED_TIME TIMESTAMP
GO

ALTER TABLE UserAccount
    ADD LAST_UPDATED_TIME TIMESTAMP
GO

ALTER TABLE UserAccount
    ADD Affiliation varchar(512)
GO
ALTER TABLE UserAccount
    ADD FirstName varchar(50)

GO
ALTER TABLE UserAccount
    ADD LastName varchar(50)
GO

ALTER TABLE UserAccount
    ADD Email varchar(50)
GO

ALTER TABLE VideoArchive
    ADD LAST_UPDATED_TIME TIMESTAMP
GO

ALTER TABLE VideoArchiveSet
    ADD LAST_UPDATED_TIME TIMESTAMP
GO

ALTER TABLE VideoFrame
    ADD LAST_UPDATED_TIME TIMESTAMP
GO

UPDATE Concept
SET ParentConceptID_FK = NULL
WHERE ParentConceptID_FK = 0
GO

UPDATE Association
	SET LAST_UPDATED_TIME='2009-01-01 00:00:00'
	WHERE LAST_UPDATED_TIME IS NULL
GO

UPDATE CameraData
    SET LAST_UPDATED_TIME='2009-01-01 00:00:00'
    WHERE LAST_UPDATED_TIME IS NULL
GO

UPDATE CameraPlatformDeployment
    SET LAST_UPDATED_TIME='2009-01-01 00:00:00'
    WHERE LAST_UPDATED_TIME IS NULL
GO

UPDATE Concept
    SET LAST_UPDATED_TIME='2009-01-01 00:00:00'
    WHERE LAST_UPDATED_TIME IS NULL
GO

UPDATE ConceptDelegate
    SET LAST_UPDATED_TIME='2009-01-01 00:00:00'
    WHERE LAST_UPDATED_TIME IS NULL
GO

UPDATE ConceptName
    SET LAST_UPDATED_TIME='2009-01-01 00:00:00'
    WHERE LAST_UPDATED_TIME IS NULL
GO

UPDATE History
    SET LAST_UPDATED_TIME='2009-01-01 00:00:00'
    WHERE LAST_UPDATED_TIME IS NULL
GO

UPDATE LinkRealization
    SET LAST_UPDATED_TIME='2009-01-01 00:00:00'
    WHERE LAST_UPDATED_TIME IS NULL
GO

UPDATE LinkTemplate
    SET LAST_UPDATED_TIME='2009-01-01 00:00:00'
    WHERE LAST_UPDATED_TIME IS NULL
GO

UPDATE Media
    SET LAST_UPDATED_TIME='2009-01-01 00:00:00'
    WHERE LAST_UPDATED_TIME IS NULL
GO

UPDATE Observation
    SET LAST_UPDATED_TIME='2009-01-01 00:00:00'
    WHERE LAST_UPDATED_TIME IS NULL
GO

UPDATE PhysicalData
    SET LAST_UPDATED_TIME='2009-01-01 00:00:00'
    WHERE LAST_UPDATED_TIME IS NULL
GO

UPDATE USAGE
    SET LAST_UPDATED_TIME='2009-01-01 00:00:00'
    WHERE LAST_UPDATED_TIME IS NULL
GO

UPDATE UserAccount
    SET LAST_UPDATED_TIME='2009-01-01 00:00:00'
    WHERE LAST_UPDATED_TIME IS NULL
GO

UPDATE VideoArchive
    SET LAST_UPDATED_TIME='2009-01-01 00:00:00'
    WHERE LAST_UPDATED_TIME IS NULL
GO

UPDATE VideoArchiveSet
    SET LAST_UPDATED_TIME='2009-01-01 00:00:00'
    WHERE LAST_UPDATED_TIME IS NULL
GO

UPDATE VideoFrame
    SET LAST_UPDATED_TIME='2009-01-01 00:00:00'
    WHERE LAST_UPDATED_TIME IS NULL
GO

-- Allow case sensitive comparison. This is needed for the uc_ConceptName constraint to work correctly. 
-- May need to drop any indices on this column first
ALTER TABLE ConceptName 
    ALTER COLUMN ConceptName VARCHAR(50) COLLATE SQL_Latin1_General_CP1_CS_AS
GO

ALTER TABLE ConceptName
    ADD CONSTRAINT uc_ConceptName UNIQUE (ConceptName)
GO

ALTER TABLE VideoArchive
	ALTER "VIDEOARCHIVENAME" NOT NULL
GO

ALTER TABLE VideoArchive
    ADD CONSTRAINT uc_VideoArchiveName UNIQUE (VideoArchiveName)
GO

-- Enforce the 1:1 relationship between VideoFrame and CameraData tables
ALTER TABLE PhysicalData
    ADD CONSTRAINT uc_VideoFrameID_FK UNIQUE (VideoFrameID_FK)
GO

-- Enforce the 1:1 relationship between VideoFrame and CameraData tables
ALTER TABLE CameraData
    ADD CONSTRAINT uc_VideoFrameID_FK UNIQUE (VideoFrameID_FK)
GO

ALTER TABLE "VARSUSER"."CONCEPTDELEGATE"
	ADD CONSTRAINT "FK_CONCEPTDELEGATE_TO_CONCEPT"
	FOREIGN KEY("CONCEPTID_FK")
	REFERENCES "VARSUSER"."CONCEPT"("ID")
GO

ALTER TABLE "VARSUSER"."CONCEPT"
	ADD CONSTRAINT "FK_CONCEPT_TO_PARENTCONCEPT"
	FOREIGN KEY("PARENTCONCEPTID_FK")
	REFERENCES "VARSUSER"."CONCEPT"("ID")
GO

ALTER TABLE "VARSUSER"."CONCEPTNAME"
	ADD CONSTRAINT "FK_CONCEPTNAME_TO_CONCEPT"
	FOREIGN KEY("CONCEPTID_FK")
	REFERENCES "VARSUSER"."CONCEPT"("ID")
GO

ALTER TABLE "VARSUSER"."HISTORY"
	ADD CONSTRAINT "FK_HISTORY_TO_CONCEPTDELEGATE"
	FOREIGN KEY("CONCEPTDELEGATEID_FK")
	REFERENCES "VARSUSER"."CONCEPTDELEGATE"("ID")
GO

ALTER TABLE "VARSUSER"."LINKREALIZATION"
	ADD CONSTRAINT "FK_LINKREALIZATION_TO_CONCEPTDELEGATE"
	FOREIGN KEY("CONCEPTDELEGATEID_FK")
	REFERENCES "VARSUSER"."CONCEPTDELEGATE"("ID")
GO

ALTER TABLE "VARSUSER"."LINKTEMPLATE"
	ADD CONSTRAINT "FK_LINKTEMPLATE_TO_CONCEPTDELEGATE"
	FOREIGN KEY("CONCEPTDELEGATEID_FK")
	REFERENCES "VARSUSER"."CONCEPTDELEGATE"("ID")
GO

ALTER TABLE "VARSUSER"."MEDIA"
	ADD CONSTRAINT "FK_MEDIA_TO_CONCEPTDELEGATE"
	FOREIGN KEY("CONCEPTDELEGATEID_FK")
	REFERENCES "VARSUSER"."CONCEPTDELEGATE"("ID")
GO

ALTER TABLE "VARSUSER"."USAGE"
	ADD CONSTRAINT "FK_USAGE_TO_CONCEPTDELEGATE"
	FOREIGN KEY("CONCEPTDELEGATEID_FK")
	REFERENCES "VARSUSER"."CONCEPTDELEGATE"("ID")
GO

ALTER TABLE "VARSUSER"."CONCEPTDELEGATE"
	ALTER "CONCEPTID_FK" NOT NULL
GO
ALTER TABLE "VARSUSER"."CONCEPTDELEGATE"
	ALTER "USAGEID_FK" NOT NULL
GO
-- Enforce the 1:1 relationship between Concept and ConceptDelegate tables
ALTER TABLE ConceptDelegate
    ADD CONSTRAINT uc_ConceptID_FK UNIQUE (ConceptID_FK)
GO

-- Fix up History tabel to our liking
RENAME COLUMN "VARSUSER"."HISTORY"."APPROVALDTG" TO "PROCESSEDDTG"
GO
RENAME COLUMN "VARSUSER"."HISTORY"."APPROVERNAME" TO "PROCESSORNAME"
GO
RENAME COLUMN "VARSUSER"."HISTORY"."REJECTED" TO "APPROVED"
GO

--
INSERT INTO UNIQUEID VALUES
  ('VideoArchiveSet', 100),
  ('CameraPlatformDeployment', 100),
  ('VideoArchive', 100),
  ('VideoFrame', 100),
  ('CameraData', 100),
  ('PhysicalData', 100),
  ('Observation', 100),
  ('Association', 100),
  ('UserName', 100)
GO

-- Make sure lat and lon are double precision
ALTER TABLE PHYSICALDATA ADD COLUMN LONGITUDE2 DOUBLE
GO
UPDATE PHYSICALDATA SET LONGITUDE2=LONGITUDE
GO
ALTER TABLE PHYSICALDATA DROP COLUMN LONGITUDE
GO
RENAME COLUMN PHYSICALDATA.LONGITUDE2 TO LONGITUDE
GO

ALTER TABLE PHYSICALDATA ADD COLUMN LATITUDE2 DOUBLE
GO
UPDATE PHYSICALDATA SET LATITUDE2=LATITUDE
GO
ALTER TABLE PHYSICALDATA DROP COLUMN LATITUDE
GO
RENAME COLUMN PHYSICALDATA.LATITUDE2 TO LATITUDE
GO

-- Add altitude and speed columns
ALTER TABLE PHYSICALDATA ADD COLUMN ALTITUDE REAL
GO
ALTER TABLE CAMERADATA ADD COLUMN SPEED REAL
GO